<%-- 
    Document   : active
    Created on : Dec 9, 2011, 2:11:43 AM
    Author     : Michael
--%>

<%@page import="java.sql.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Most Active</title>
    </head>
    <body>
        <input type="button" value="Home" onclick="window.location='/TheLoop/gohome.jsp'"/>&nbsp;
          <input type="button" value="Log Out" onclick="window.location='/TheLoop/logout.jsp'"/>
        <h1>Most Active Items</h1>
        
        <table width="800" border="1">
            <tr>
                <td>Company</td>
                <td>Item Name</td>
                <td>Units Sold</td>
            </tr>
        <%  Connection conn = null;
            try
            {
                conn = new DBClasses.GetConnection().getConnection();
                ResultSet rs = conn.createStatement().executeQuery("SELECT Company, ItemName, SUM(NumUnits) AS UnitsSold " + 
                            "FROM advertisement a, sales s " + 
                            "WHERE AdvertisementID=s.AdID " +
                            "GROUP BY AdvertisementID " +
                            "ORDER BY UnitsSold DESC " +
                            "LIMIT 10");
                while(rs.next())
                {
%>
                   <tr>
                       <td><%=rs.getString("Company")%></td>
                       <td><%=rs.getString("ItemName")%></td>
                       <td><%=rs.getString("UnitsSold")%></td>
                 </tr> 
<%
                }
            }
            catch(Exception e)
            {
                e.printStackTrace();
                response.sendRedirect("/TheLoop/gohome.jsp");
            }
            finally
            {
                try { conn.close(); }
                catch(Exception e) { response.sendRedirect("/TheLoop/gohome.jsp"); }
                
            }
        %>
        </table>
        </body>
</html>
